# 导入库--注意本段代码不适用于python2
import sys
import pymysql
from PySide2.QtUiTools import QUiLoader
from PySide2.QtWidgets import QTableWidgetItem

from mian_ui import Ui_MainWindow
from select import select_ui
from insert import insert_ui
from delete import delete_ui
from update import update_ui
from PyQt5 import QtWidgets
from PyQt5.QtWidgets import QMessageBox


class load(Ui_MainWindow, select_ui, insert_ui, delete_ui, update_ui):
    def __init__(self, *args, **kwargs):
        super(Ui_MainWindow, self).__init__(*args, **kwargs)
        super(select_ui, self).__init__(*args, **kwargs)
        super(insert_ui, self).__init__(*args, **kwargs)
        super(delete_ui, self).__init__(*args, **kwargs)
        super(update_ui, self).__init__(*args, **kwargs)
        self.ui2 = QUiLoader().load('DB_main.ui')
        self.ui3 = QUiLoader().load('select.ui')
        self.ui4 = QUiLoader().load('insert.ui')
        self.ui5 = QUiLoader().load('delete.ui')
        self.ui6 = QUiLoader().load('update.ui')
        # 功能函数触发
        self.ui2.pushButton.clicked.connect(self.INSERT)
        self.ui2.pushButton_2.clicked.connect(self.SELECT)
        self.ui2.pushButton_3.clicked.connect(self.DELETE)
        self.ui2.pushButton_4.clicked.connect(self.UPDATE)
        self.ui2.pushButton_5.clicked.connect(self.Signin_DB)
        # 基本数据库表的数据查看功能
        self.ui2.actionS.triggered.connect(lambda: self.browse(self.ui2.actionS))
        self.ui2.actionP.triggered.connect(lambda: self.browse(self.ui2.actionP))
        self.ui2.actionJ.triggered.connect(lambda: self.browse(self.ui2.actionJ))
        self.ui2.actionSPJ.triggered.connect(lambda: self.browse(self.ui2.actionSPJ))
        # 查询数据
        self.SELECT_SNO = ""
        self.ui3.radioButton.clicked.connect(lambda: self.SELECT_UI(self.ui3.radioButton))
        self.ui3.radioButton_2.clicked.connect(lambda: self.SELECT_UI(self.ui3.radioButton_2))
        self.ui3.pushButton.clicked.connect(self.SELECT_show)
        self.select_SNO = ""
        self.select_Status = ""
        self.select_S = "SELECT Sno,Sname,Sstatus,Scity  FROM s;"
        self.select_P = "SELECT Pno, Pname, Pcolor, Pweight  FROM p;"
        self.select_J = "SELECT Jno, Jname, Jcity FROM j;"
        self.select_SPJ = "SELECT Sno,Pno,Jno,QTY  FROM spj;"
        # 信息插入
        self.ui4.pushButton.clicked.connect(self.insert_S)
        self.ui4.pushButton_2.clicked.connect(self.insert_SPJ)
        self.insert_S = ""
        self.insert_SPJ = ""
        # 信息删除
        self.ui5.pushButton.clicked.connect(self.delete_clicked)
        self.delete_SNO = ""
        self.delete_spj = ""
        self.delete_s = ""
        # 信息修改
        self.ui6.pushButton.clicked.connect(self.update_clicked)
        self.update_SNO = ""
        self.update_spj = ""

        self.host = ""
        self.user = ""
        self.passwd = ""
        self.db = ""
        self.DB_islive = False
        self.SELECT_type = ""
        # self.host = 'localhost'
        # self.user = 'root'
        # self.passwd = '123'
        # self.db = 'text1'

    def Signin_DB(self, ):
        """
        连接数据库
        """
        # 打开数据库连接
        self.host = self.ui2.lineEdit.text()
        self.user = self.ui2.lineEdit_2.text()
        self.passwd = self.ui2.lineEdit_3.text()
        self.db = self.ui2.lineEdit_4.text()
        if self.host == "" or self.user == "":
            QMessageBox.information(None, "提示", "请输入host和用户名", QMessageBox.Ok)
        elif self.passwd == "":
            QMessageBox.information(None, "提示", "请输入密码", QMessageBox.Ok)
        elif self.db == "":
            QMessageBox.information(None, "提示", "请输入数据库名称", QMessageBox.Ok)
        else:
            global conn
            conn = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.db)
            self.ui2.textBrowser.append("<h4>数据库连接成功</h4>")
            self.DB_islive = True

    # 显示SQL语句执行
    def read(self, surface):
        self.ui2.tableWidget.clear()
        cur = conn.cursor()
        cur.execute(surface)
        res = cur.fetchall()
        for i in range(len(res)):
            rowslist = res[i]
            row = self.ui2.tableWidget.rowCount()
            self.ui2.tableWidget.insertRow(row)
            for j in range(len(res[0])):
                newItem = QTableWidgetItem(str(rowslist[j]))
                self.ui2.tableWidget.setItem(i, j, newItem)

    # 其他SQL语句执行
    def SQL_exe(self, surface):
        self.ui2.tableWidget.clear()
        cur = conn.cursor()
        cur.execute(surface)

    # 浏览表选择
    def browse(self, item):
        if self.DB_islive:
            if item == self.ui2.actionS:
                self.read(self.select_S)
                self.ui2.textBrowser.append("<h4>S表已成功显示</h4>")
            elif item == self.ui2.actionP:
                self.read(self.select_P)
                self.ui2.textBrowser.append("<h4>P表已成功显示</h4>")
            elif item == self.ui2.actionJ:
                self.read(self.select_J)
                self.ui2.textBrowser.append("<h4>J表已成功显示</h4>")
            elif item == self.ui2.actionSPJ:
                self.read(self.select_SPJ)
                self.ui2.textBrowser.append("<h4>SPJ表已成功显示</h4>")
        else:
            self.ui2.textBrowser.append("<h4>数据库未连接，请先连接数据库</h4>")

    # 插入界面
    def INSERT(self):
        if self.DB_islive:
            self.ui4.show()
        else:
            self.ui2.textBrowser.append("<h4>数据库未连接，请先连接数据库</h4>")

    # 供应商表信息插入
    def insert_S(self):
        SNO = self.ui4.lineEdit.text()
        Sname = self.ui4.lineEdit_2.text()
        Sstatus = self.ui4.lineEdit_3.text()
        Scity = self.ui4.lineEdit_4.text()
        if SNO == "" or Sname == "" or Sstatus == "" or Scity == "":
            QMessageBox.information(None, "提示", "请输入完整信息", QMessageBox.Ok)
        else:
            self.insert_S = "INSERT INTO S(Sno,Sname,Sstatus,Scity) VALUES (\"{}\",\"{}\",\"{}\",\"{}\");".format(SNO,
                                                                                                                  Sname,
                                                                                                                  Sstatus,
                                                                                                                  Scity)
            self.SQL_exe(self.insert_S)
            QMessageBox.information(None, "提示", "供应商表信息插入成功", QMessageBox.Ok)
        pass

    # 供应商情况表信息插入
    def insert_SPJ(self):
        Sno = self.ui4.lineEdit_9.text()
        Pno = self.ui4.lineEdit_10.text()
        Jno = self.ui4.lineEdit_11.text()
        QTY = self.ui4.lineEdit_12.text()
        if Sno == "" or Jno == "" or Pno == "" or QTY == "":
            QMessageBox.information(None, "提示", "请输入完整信息", QMessageBox.Ok)
        else:
            self.insert_SPJ = "INSERT INTO SPJ(Sno,Pno,Jno,QTY) VALUES (\"{}\",\"{}\",\"{}\",\"{}\");".format(Sno,
                                                                                                              Pno,
                                                                                                              Jno,
                                                                                                              QTY)
            self.SQL_exe(self.insert_SPJ)
            QMessageBox.information(None, "提示", "供应商情况表信息插入成功", QMessageBox.Ok)
        pass

    # 查询页面
    def SELECT(self):
        if self.DB_islive:
            self.ui3.show()
        else:
            self.ui2.textBrowser.append("<h4>数据库未连接，请先连接数据库</h4>")

    # 供应商状态和供应商代码选择
    def SELECT_UI(self, radioButton):
        if self.DB_islive:
            if radioButton == self.ui3.radioButton:
                self.SELECT_type = "供应商代码"
            elif radioButton == self.ui3.radioButton_2:
                self.SELECT_type = "供应商状态"

    # 点击查询
    def SELECT_show(self):
        self.SELECT_SNO = self.ui3.lineEdit.text()
        if self.SELECT_type == "供应商代码":
            self.select_SNO = "SELECT Sno,Sname,Sstatus,Scity FROM s WHERE SNO=\"{}\";".format(self.SELECT_SNO)
            self.read(self.select_SNO)
            self.ui2.textBrowser.append("<h4>根据供应商代码查询供应商信息成功</h4>")

        elif self.SELECT_type == "供应商状态":
            self.select_Status = "SELECT Sno,Sname,Sstatus,Scity FROM s WHERE Sstatus=\"{}\";".format(self.SELECT_SNO)
            self.read(self.select_Status)
            self.ui2.textBrowser.append("<h4>根据供应商状态查询供应商信息成功</h4>")

    # 删除界面
    def DELETE(self):
        if self.DB_islive:
            self.ui5.show()
        else:
            self.ui2.textBrowser.append("<h4>数据库未连接，请先连接数据库</h4>")

    # 点击删除
    def delete_clicked(self):
        self.delete_SNO = self.ui5.lineEdit.text()
        self.delete_spj = "DELETE FROM SPJ WHERE SNO=\"{}\";".format(self.delete_SNO)
        self.delete_s = "DELETE FROM S WHERE SNO=\"{}\";".format(self.delete_SNO)
        self.read(self.delete_spj)
        self.read(self.delete_s)
        self.ui2.textBrowser.append("<h4>删除指定供应商代码的供应商信息成功</h4>")

    # 修改界面
    def UPDATE(self):
        if self.DB_islive:
            self.ui6.show()
        else:
            self.ui2.textBrowser.append("<h4>数据库未连接，请先连接数据库</h4>")

    # 点击修改信息
    def update_clicked(self):
        self.update_SNO = self.ui6.lineEdit.text()
        self.update_spj = "UPDATE SPJ SET QTY=QTY*1.1 WHERE SNO=\"{}\";".format(self.delete_SNO)
        self.ui2.textBrowser.append("<h4>QTY 增加 10%修改成功</h4>")


if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    myWin = load()
    myWin.ui2.show()
    sys.exit(app.exec_())
